<?php
    define('WP_USE_THEMES', false); 
    
    require('./wp-blog-header.php');
    require( ABSPATH . 'wp-admin/includes/upgrade.php' );

    // http://codex.wordpress.org/Class_Reference/wpdb
    // global $wpdb;
    // always call to ensure any SQL errors are shown
    // $wpdb->show_errors(true);
    // NOTE: Also ensure display_errors = On is set in php.ini on your machine 
    class DBHandler
    {
        function DeleteTables()
        {
            global $wpdb;
            $wpdb->show_errors(true);

            // delete child table first 
            $tablename = $wpdb->prefix . "enrollments";
            $sql = "DROP TABLE IF EXISTS $tablename;";
            $wpdb->query($sql);

            // delete parent table later
            $tablename = $wpdb->prefix . "courses";
            $sql = "DROP TABLE IF EXISTS $tablename;";
            $wpdb->query($sql);

            return "Tables deleted";
        }

        function CreateTables()
        {
            global $wpdb;
            $wpdb->show_errors(true);

            $usersTableName = $wpdb->prefix . "users";

            // create parent table first
            $coursesTableName = $wpdb->prefix . "courses";
            $sql = "CREATE TABLE $coursesTableName (
                id bigint(20) NOT NULL AUTO_INCREMENT,
                name varchar(40) NOT NULL,
                quarter varchar(20) NOT NULL,
                year smallint unsigned NOT NULL,
                facultyid bigint(20) unsigned,
                PRIMARY KEY id (id),
                FOREIGN KEY (facultyid) REFERENCES $usersTableName (id)
            );";
            $wpdb->query($sql);

            // create child tables later
            $enrollmentsTableName = $wpdb->prefix . "enrollments";
            $sql = "CREATE TABLE $enrollmentsTableName (
                id bigint(20) NOT NULL AUTO_INCREMENT,
                courseid bigint(20),
                studentid bigint(20) unsigned,
                PRIMARY KEY id (id),
                FOREIGN KEY (courseid) REFERENCES $coursesTableName (id),
                FOREIGN KEY (studentid) REFERENCES $usersTableName (id)
            );";
            $wpdb->query($sql);
        }

        function RecreateTables() 
        {
            self::DeleteTables();
            self::CreateTables();

            return "Tables recreated<br/>";
        }

        function GetAllFaculty()
        {
            global $wpdb;
            $wpdb->show_errors(true);
            
            $rows = $wpdb->get_results("SELECT u.ID as Id, u.display_name as Name 
                FROM wp_users u INNER JOIN wp_usermeta up 
                ON u.id = up.user_id 
                WHERE up.meta_key = 'wp_capabilities' AND up.meta_value LIKE '%author%';");

            return $rows;
        }

        function GetCourse($courseId)
        {
            global $wpdb;
            $wpdb->show_errors(true);

            $tablename = $wpdb->prefix . "courses";

            $rows = $wpdb->get_row("SELECT c.name as Name, c.quarter as Quarter, c.year as Year, c.facultyid as FacultyId
                FROM $tablename as c WHERE c.id = $courseId");

            return $rows;
        }

        function GetAllCourses()
        {
            global $wpdb;
            $wpdb->show_errors(true);

            $tablename = $wpdb->prefix . "courses";

            $rows = $wpdb->get_results("SELECT c.Id as Id, c.name as Name, c.quarter as Quarter, c.year as Year, u.display_name as FacultyName
                FROM $tablename as c INNER JOIN wp_users u
                ON c.facultyid = u.id");

            return $rows;
        }

        function AddCourse($courseName, $quarter, $year, $facultyId)
        {
            global $wpdb;
            $wpdb->show_errors(true);

            $tablename = $wpdb->prefix . "courses";
            
            $wpdb->insert($tablename, array ('name' => $courseName, 'quarter' => $quarter, 'year' => $year, 'facultyid' => $facultyId));
        }

        function DeleteCourse($courseId)
        {
            global $wpdb;
            $wpdb->show_errors(true);

            $tablename = $wpdb->prefix . "courses";

            $wpdb->query("DELETE FROM $tablename WHERE id = $courseId");
        }

        function UpdateCourse($courseId, $courseName, $quarter, $year, $facultyId)
        {
            global $wpdb;
            $wpdb->show_errors(true);

            $tablename = $wpdb->prefix . "courses";
            
            $wpdb->update($tablename, array ('name' => $courseName, 'quarter' => $quarter, 'year' => $year, 'facultyid' => $facultyId), array( 'Id' => $courseId ));
        }
    }
?>

